﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;
namespace BookStore.Models
{
    public class UsersModel
    {
        private string connectionString;
        private SqlConnection connect;

        public UsersModel()
        {
            connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString;
            connect = new SqlConnection(connectionString);
            
        }
        public int Add_Account(string []account)
        {
            try
            {
                connect.Open();

                string selectSQL = "INSERT INTO Users (user_code,user_name,password,email,phone,address,cmnd,created_date,updated_date,active,active_code,user_type) VALUES ('" + account[0] + "','" + account[0] + "',N'" + account[1] + "','" + account[3] + "','" + account[5] + "',N'" + account[2] + "','" + account[4] + "'," + account[7] + "," + account[8] + "," + account[9] + ",'" + account[10] + "',3)";
                SqlCommand cmd = new SqlCommand(selectSQL, connect);
                cmd.ExecuteNonQuery();
            }
            catch (Exception err)
            {
                return -3;
            }
            finally
            {
                connect.Close();
            }

            return 1;
           
        }

        public int Check_Account(string[] account)
        {
            int result = 0;
            int result_email = 0;

            try
            {
                connect.Open();
                string selectSQL = "SELECT count(*) as total FROM Users WHERE user_name = '" + account[0]+"'";
                SqlCommand cmd = new SqlCommand(selectSQL, connect);

                SqlDataReader reader;
                reader = cmd.ExecuteReader();
                if (reader.Read())
                    result = Convert.ToInt32(reader["total"]);
                reader.Close();
                if (result != 0) return -1;

                string selectSQL_email = "SELECT count(*) as total FROM Users WHERE email = '" + account[1]+"'";
                SqlCommand cmd_email = new SqlCommand(selectSQL_email, connect);

                SqlDataReader reader_email;
                reader_email = cmd_email.ExecuteReader();
                if (reader_email.Read())
                    result_email = Convert.ToInt32(reader_email["total"]);
                reader_email.Close();
                if (result_email != 0) return -2;
            }
            catch (Exception err)
            {
                return -3;
            }
            finally
            {
                connect.Close();
            }

            return 1;
           
        }
        public int Check_Edit_Account(int user_id, string email)
        {
            int result_email = 0;

            try
            {
                connect.Open();

                string selectSQL_email = "SELECT count(*) as total FROM Users WHERE email = '" + email + "' and user_id !=" + user_id;
                SqlCommand cmd_email = new SqlCommand(selectSQL_email, connect);

                SqlDataReader reader_email;
                reader_email = cmd_email.ExecuteReader();
                if (reader_email.Read())
                    result_email = Convert.ToInt32(reader_email["total"]);
                reader_email.Close();
                if (result_email != 0) return -2;

            }
            catch (Exception err)
            {
                return -3;
            }
            finally
            {
                connect.Close();
            }

            return 1;
        }
        public int Edit_Account(int user_id, string[] account)
        {
            int result_email = 0;

            try
            {

                connect.Open();
                string selectSQL = "UPDATE Users SET phone='" + account[1] + "',address=N'" + account[0] + "',cmnd='" + account[2] + "',email='" + account[3] + "',updated_date=" + account[4] + " where user_id=" + user_id;
                SqlCommand cmd = new SqlCommand(selectSQL, connect);
                cmd.ExecuteNonQuery();
            }
            catch (Exception err)
            {
                return -3;
            }
            finally
            {
                connect.Close();
            }

            return 1;
        }
        public int Active_Account(string active)
        {
            try
                {
                    connect.Open();
                    string  selectSQL = "UPDATE Users set active=1 where active_code='"+active+"'";   
                    SqlCommand cmd = new SqlCommand(selectSQL, connect);
                    cmd.ExecuteNonQuery();
                }
                catch (Exception err)
                {
                    return -1;
                }
                finally
                {
                    connect.Close();
                }
            return 1;
        }

        public int Check_User(string user)
        {
            int result = 0;
            try
            {
                connect.Open();
                string selectSQL = "SELECT user_id,active FROM Users WHERE user_name = '" + user + "'";
                SqlCommand cmd = new SqlCommand(selectSQL, connect);
                SqlDataReader reader;
                reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    result = Convert.ToInt32(reader["user_id"]);
                }
                else
                    result = -1;
                reader.Close();

                if (result == -1) return -1;
                else return result;
            }
            catch (Exception err)
            {
                return -1;
            }
            finally
            {
                connect.Close();
            }
        }
        public int Check_Pass(string pass1,int user_id)
        {
             int result = 0;
             string pass = null;
             int active = 0;
            try
            {
                connect.Open();
                string selectSQL = "SELECT *  FROM Users WHERE user_id = " + user_id;
                SqlCommand cmd = new SqlCommand(selectSQL, connect);
                SqlDataReader reader;
                reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    pass = reader["password"].ToString();
                    active = Convert.ToInt32(reader["active"].ToString());
                    if (pass == pass1)
                        result = 1;
                    else
                        return -1;

                }
                reader.Close();

                if (result == -1) return -1;
                else if (active == 0) return -2;
                else if (active == 2) return -3;
                else return 1;
            }
            catch (Exception err)
            {
                return -1;
            }
            finally
            {
                connect.Close();
            }
        }

        public int Check_Change_Password(int user_id, string old, string new_)
        {
            int result = 0;
            string pass=null;
            try
            {
                connect.Open();
                string selectSQL = "SELECT *  FROM Users WHERE user_id = " + user_id;
                SqlCommand cmd = new SqlCommand(selectSQL, connect);
                SqlDataReader reader;
                reader = cmd.ExecuteReader();
                if (reader.Read())
                {    pass = reader["password"].ToString();
                    if(pass==old)
                        result = 1;
                    else
                         result = -1;

                }
                reader.Close();
                if (result == -1 || result == 0) return -1; 
            }
            catch (Exception err)
            {
                return -1;
            }
            finally
            {
                connect.Close();
            }
         return 1;
        }

        public int Change_Password(int user_id,string old, string new_)
        {
            try
            {
                connect.Open();
                

                    string selectSQL_update = "UPDATE Users set password=N'" + new_ + "' where user_id=" + user_id + "";
                    SqlCommand cmd_update = new SqlCommand(selectSQL_update, connect);
                    cmd_update.ExecuteNonQuery();
                
            }
            catch (Exception err)
            {
                return -1;
            }
            finally
            {
                connect.Close();
            }
         return 1;
        }


        public int Change_Pass(string user_name,string pass )
        {
            try
            {
                connect.Open();
                string selectSQL_update = "UPDATE Users set password=N'" + pass + "' where user_name='" + user_name + "'";
                    SqlCommand cmd_update = new SqlCommand(selectSQL_update, connect);
                    cmd_update.ExecuteNonQuery();

            }
            catch (Exception err)
            {
                return -1;
            }
            finally
            {
                connect.Close();
            }
         return 1;
        }

        public string[] getDetailUserById(int ID)
        {
            string[] result = new string[5];
            try
            {
                connect.Open();
                string selectSQL = "SELECT user_name,email,phone,address,cmnd FROM Users WHERE user_id = " + ID.ToString();
                SqlCommand cmd = new SqlCommand(selectSQL, connect);

                SqlDataReader reader;
                reader = cmd.ExecuteReader();
                if (reader.Read())
                {
                    result[0] = reader["email"].ToString();
                    result[1] = reader["phone"].ToString();
                    result[2] = reader["cmnd"].ToString();
                    result[3] = reader["address"].ToString();
                    result[4] = reader["user_name"].ToString();

                }
                reader.Close();
            }
            catch (Exception err)
            {
                return null;
            }
            finally
            {
                connect.Close();
            }

            return result;
        }

        public string getUserNamebyID(int ID)
        {
            string result = "";
            try
            {
                connect.Open();
                string selectSQL = "SELECT user_name FROM Users WHERE user_id = " + ID.ToString();
                SqlCommand cmd = new SqlCommand(selectSQL, connect);

                SqlDataReader reader;
                reader = cmd.ExecuteReader();
                if (reader.Read())
                    result = reader["user_name"].ToString();

                reader.Close();
            }
            catch (Exception err)
            {
                return null;
            }
            finally
            {
                connect.Close();
            }

            return result;
        }

        public int Check_User_Email(string username, string email)
        {
            int result = 0;
            int result_email = 0;
            try
            {
                connect.Open();

                string selectSQL = "SELECT count(*) as total FROM Users WHERE user_name = '" + username + "'";
                SqlCommand cmd = new SqlCommand(selectSQL, connect);

                SqlDataReader reader;
                reader = cmd.ExecuteReader();
                if (reader.Read())
                    result = Convert.ToInt32(reader["total"]);
                reader.Close();
                if (result == 0 || result == -1) return -1;

                string selectSQL_email = "SELECT count(*) as total FROM Users WHERE email = '" + email + "'";
                SqlCommand cmd_email = new SqlCommand(selectSQL_email, connect);

                SqlDataReader reader_email;
                reader_email = cmd_email.ExecuteReader();
                if (reader_email.Read())
                    result_email = Convert.ToInt32(reader_email["total"]);
                reader_email.Close();
                if (result_email == 0 || result_email == -1) return -1;

               
            }
            catch (Exception err)
            {
                return -3;
            }
            finally
            {
                connect.Close();
            }

            return 1;
           
        }
        public string getUserNamebyID1(int ID)
        {
            string result = "";
            try
            {
                connect.Open();
                string selectSQL = "SELECT user_name FROM Users WHERE user_id = " + ID.ToString();
                SqlCommand cmd = new SqlCommand(selectSQL, connect);

                SqlDataReader reader;
                reader = cmd.ExecuteReader();
                if (reader.Read())
                    result = reader["user_name"].ToString();

                reader.Close();
            }
            catch (Exception err)
            {
                return null;
            }
            finally
            {
                connect.Close();
            }

            return result;
        }

        public int getUserIDByUserName(string user)
        {
            int result=0;
            try
            {
                connect.Open();
                string selectSQL = "SELECT user_id FROM Users WHERE user_name = '" + user.ToString()+"'";
                SqlCommand cmd = new SqlCommand(selectSQL, connect);

                SqlDataReader reader;
                reader = cmd.ExecuteReader();
                if (reader.Read())
                    result = Convert.ToInt32(reader["user_id"]);

                reader.Close();
            }
            catch (Exception err)
            {
                return 0;
            }
            finally
            {
                connect.Close();
            }

            return result;
        }
    }
}
